package dao.mysql;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import util.DataBaseConnection;

import bean.Boleta;

import dao.Entidad;

public class MySQLBoletaDAO implements Entidad<Boleta>{

	@Override
	public Boleta insert(Boleta t) {
		Connection con=null;
		try {
			con=DataBaseConnection.openConnection();
			String sql="INSERT INTO documento_pago(fecha,hora,descripcion ,id_cliente,";
			sql = sql + "id_empleado) VALUES(?,?,?,?,?,?)";
			PreparedStatement ps=con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setObject(1, t.getFecha());
			ps.setObject(2, t.getHora());
			ps.setString(3, t.getDescripcion());
			ps.setInt(4, t.getId_cliente());
			ps.setInt(5, t.getId_empleado());
			ps.executeUpdate();
			ResultSet rs=ps.getGeneratedKeys();
			if(rs.next()){
				int id=rs.getInt(1);
				t.setId_documento_pago(id);
			}
			sql="INSERT INTO boleta(id_boleta, id_documento_pago) VALUES (?,?)";
			ps=con.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
			ps.setInt(1, t.getId_boleta());
			ps.setInt(2, t.getId_documento_pago());
			ps.executeUpdate();
			rs=ps.getGeneratedKeys();
			if(rs.next()){
				int id=rs.getInt(1);
				t.setId_boleta(id);
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return t;
	}

	@Override
	public int update(Boleta t) {
		Connection con=null;
		int rows = -1;
		try {
			con=DataBaseConnection.openConnection();
			String sql="UPDATE documento_pago SET fecha=?,hora=?,descripcion=?,";
			sql = sql +	"id_cliente=?,id_empleado = ? WHERE id_documento_pago= ?";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setObject(1, t.getFecha());
			ps.setObject(2, t.getHora());
			ps.setString(3, t.getDescripcion());
			ps.setInt(4, t.getId_cliente());
			ps.setInt(5, t.getId_empleado());
			ps.setInt(6, t.getId_documento_pago());
			rows =ps.executeUpdate();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return rows;
	}

	@Override
	public int delete(Boleta t) {
		Connection con=null;
		int rows=-1;
		try {
			con=DataBaseConnection.openConnection();
			String sql="DELETE FROM boleta WHERE id_boleta=?";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, t.getId_boleta());
			rows=ps.executeUpdate();
			System.out.println(rows);
			
			con=DataBaseConnection.openConnection();
			sql="DELETE FROM documento_pago WHERE id_documento_pago=?";
			ps=con.prepareStatement(sql);
			ps.setInt(1, t.getId_documento_pago());
			rows=ps.executeUpdate();
			System.out.println(rows);
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return rows;
	}

	@Override
	public Boleta findById(Boleta t) {
		Connection con=null;
		try {
			con=DataBaseConnection.openConnection();
			String sql="SELECT * FROM documento_pago d, boleta b WHERE d.id_documento_pago=? ";
			sql=" and d.id_documento_pago = b.id_documento_pago";
			PreparedStatement ps=con.prepareStatement(sql);
			ps.setInt(1, t.getId_documento_pago());
			ResultSet rs=ps.executeQuery();
			while(rs.next()){
				t.setId_documento_pago(rs.getInt("id_documento_pago"));
				t.setFecha(rs.getDate("fecha"));
				t.setHora(rs.getDate("hora"));
				t.setDescripcion(rs.getString("descripcion"));
				t.setId_cliente(rs.getInt("id_cliente"));
				t.setId_empleado(rs.getInt("id_empleado"));	
				t.setId_boleta(rs.getInt("id_boleta"));
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return t;
	}

	@Override
	public List<Boleta> listAll() {
		List<Boleta> documentos = new ArrayList<Boleta>();
		Connection con=null;
		try {
			con=DataBaseConnection.openConnection();
			String sql="Select * from documento_pago d, boleta b where d.id_documento_pago = b.id_documento_pago";
			Statement st=con.createStatement();
			ResultSet rs=st.executeQuery(sql);
			while(rs.next()){
				Boleta t = new Boleta();
				t.setId_documento_pago(rs.getInt("id_documento_pago"));
				t.setFecha(rs.getDate("fecha"));
				t.setHora(rs.getDate("hora"));
				t.setDescripcion(rs.getString("descripcion"));
				t.setId_cliente(rs.getInt("id_cliente"));
				t.setId_empleado(rs.getInt("id_vendedor"));	
				t.setId_boleta(rs.getInt("id_boleta"));
				documentos.add(t);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			DataBaseConnection.closeConnection(con);
		}
		return documentos;
	}

	@Override
	public List<Boleta> listAllCustom(Object... objects) {
		// TODO Auto-generated method stub
		return null;
	}

}
